/*
 * 作者：姚俊
 * 功能：学生的科研项目的service
 * 日期：2015-07-01
 */
package admin.service.fruits;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Stuproject;
import teacher.bean.Teacher;
import database.dao.MysqlCrud;

public class StudentProjectService {
	
	/**
	 * @category 根据名称对查询所有科研项目进行分页
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return papers List<Paper> 返回查询到的论文列表
	 * 
	 */
	public List<Stuproject> getSegregateStuProjectByAll(Stuproject stuProject,int pageNow,int pageSize) {
		
		String sql="select * from stuproject ";
		int j=0;
		String sql2=" stuproId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
		List<Stuproject> stuProjects=new ArrayList<Stuproject>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();

		if(stuProject!=null)
		{
			if(stuProject.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuProject.getStuId()));
			}
			if(stuProject.getProlevel()!=null)
			{
				sql1.add(" prolevel=? ");
				paras.add(String.valueOf(stuProject.getProlevel()));
			}
			if(stuProject.getProsource()!=null)
			{
				sql1.add(" prosource like ? ");
				paras.add("%"+String.valueOf(stuProject.getProsource())+"%");
			}
			if(stuProject.getProname()!=null)
			{
				sql1.add(" proname like ? ");
				paras.add("%"+String.valueOf(stuProject.getProname())+"%");
			}
		}
		
		for(int i=0;i<sql1.size();i++)
		{
			if(i==0)
				sql+="where"+sql1.get(i);
			else
				sql+="and"+sql1.get(i);
		}
		
		//加入最后一个,该字符是用于分页
		if(sql1.size()==0)
			sql+=sql2;
		else
			sql+="and"+sql2;
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<lists.size()){
			Map map=lists.get(j);
			stuProjects.add(new MapToBean().MapToStuproject(map));
			j++;
		}
		return stuProjects;
	}
	
	
	/**
	 * @category 根据所有条件对查询所有科研项目进行分页，针对不同的方向
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return papers List<Paper> 返回查询到的论文列表
	 * 
	 */
	public List<Stuproject> getSegregateStuProjectByDirection(Stuproject stuProject,Teacher teacher,int pageNow,int pageSize) {
		
		String sql="select * from stuproject as project,teacher as tea,students as stu where tea.teaId=stu.teaId and stu.stuId=project.stuId and tea.direction=? ";
		int j=0;
		String sql2=" stuproId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
		List<Stuproject> stuProjects=new ArrayList<Stuproject>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		paras.add(String.valueOf(teacher.getDirection()));
		
		if(stuProject!=null)
		{
			if(stuProject.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuProject.getStuId()));
			}
			if(stuProject.getProlevel()!=null)
			{
				sql1.add(" prolevel=? ");
				paras.add(String.valueOf(stuProject.getProlevel()));
			}
			if(stuProject.getProsource()!=null)
			{
				sql1.add(" prosource like ? ");
				paras.add("%"+String.valueOf(stuProject.getProsource())+"%");
			}
			if(stuProject.getProname()!=null)
			{
				sql1.add(" proname like ? ");
				paras.add("%"+String.valueOf(stuProject.getProname())+"%");
			}
		}
		
		for(int i=0;i<sql1.size();i++)
		{
				sql+="and"+sql1.get(i);
		}
		
		//加入最后一个,该字符是用于分页
		sql+="and"+sql2;
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<lists.size()){
			Map map=lists.get(j);
			stuProjects.add(new MapToBean().MapToStuproject(map));
			j++;
		}
		return stuProjects;
	}
	
	/**
	 * @category 根据不同条件对查询所有科研项目的数量,针对特定的方向
	 * 
	 */
	public int getCountStuProjectByDirection(Stuproject stuProject,Teacher teacher) {
		
		String sql="select count(*) from stuproject as project,teacher as tea,students as stu where tea.teaId=stu.teaId and stu.stuId=project.stuId and tea.direction=? ";
		int count=0;
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		paras.add(String.valueOf(teacher.getDirection()));
		
		if(stuProject!=null)
		{
			if(stuProject.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuProject.getStuId()));
			}
			if(stuProject.getProlevel()!=null)
			{
				sql1.add(" prolevel=? ");
				paras.add(String.valueOf(stuProject.getProlevel()));
			}
			if(stuProject.getProsource()!=null)
			{
				sql1.add(" prosource like ? ");
				paras.add("%"+String.valueOf(stuProject.getProsource())+"%");
			}
			if(stuProject.getProname()!=null)
			{
				sql1.add(" proname like ? ");
				paras.add("%"+String.valueOf(stuProject.getProname())+"%");
			}
		}
		
		for(int i=0;i<sql1.size();i++)
		{
				sql+="and"+sql1.get(i);
		}
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		Map map=lists.get(0);
		count=Integer.parseInt(map.get(0).toString());
		return count;
	}
	
	
	/**
	 * @category 根据不同条件对查询所有科研项目的数量，针对特定的方向
	 * 
	 */
	public int getCountStuProjectByAll(Stuproject stuProject) {
		
		String sql="select count(*) from stuproject ";
		int count=0;
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		
		if(stuProject!=null)
		{
			if(stuProject.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuProject.getStuId()));
			}
			if(stuProject.getProlevel()!=null)
			{
				sql1.add(" prolevel=? ");
				paras.add(String.valueOf(stuProject.getProlevel()));
			}
			if(stuProject.getProsource()!=null)
			{
				sql1.add(" prosource like ? ");
				paras.add("%"+String.valueOf(stuProject.getProsource())+"%");
			}
			if(stuProject.getProname()!=null)
			{
				sql1.add(" proname like ? ");
				paras.add("%"+String.valueOf(stuProject.getProname())+"%");
			}
		}
		
		for(int i=0;i<sql1.size();i++)
		{
			if(i==0)
				sql+="where"+sql1.get(i);
			else
				sql+="and"+sql1.get(i);
		}
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		Map map=lists.get(0);
		count=Integer.parseInt(map.get(0).toString());
		return count;
	}

}
